/*
This Do File creates the Housing Price Index for years 1985-1990 by state and census
division.

Data Source: https://www.fhfa.gov/DataTools/Downloads/Pages/House-Price-Index-Datasets.aspx
Additional Data
Annual House Price Indexes (see Working Papers 16-01, 16-02, and 16-04)
States

Date: 7/29/19
*/
clear
cd "C:\Users\joann\Dropbox\Back Up\Documents\Migration Paper"


import excel using HPI_AT_BDL_state.xlsx, firstrow cellrange(A7:H2251) clear

destring, replace

keep if Year >=1985 & Year <=1990
reshape wide AnnualChange HPI HPIwith1990base HPIwith2000base, i(State) j(Year)

g Region = .
replace Region = 1 if State == "Connecticut" | State == "Maine" | State == "New Hampshire" | ///
State == "Rhode Island" | State == "Vermont" | State == "Massachusetts"
replace Region = 2 if State == "New Jersey" | State == "New York" | State == "Pennsylvania"
replace Region = 3 if State == "Illinois" | State == "Indiana" | State == "Michigan" ///
|State == "Ohio" | State == "Wisconsin"
replace Region = 4 if State == "Iowa" | State == "Kansas" | State =="Minnesota" | ///
State == "Missouri" | State == "Nebraska" | State == "North Dakota" | State == "South Dakota"
replace Region = 5 if State == "Delaware" | State == "District of Columbia" | State == "Florida" | ///
State == "Georgia" | State == "Maryland" | State == "North Carolina" | State == "South Carolina" ///
|State == "Virginia" | State == "West Virginia" 
replace Region = 6 if State == "Alabama" | State == "Kentucky" | State == "Mississippi" ///
| State == "Tennessee"
replace Region = 7 if State == "Arkansas" | State == "Louisiana" | State == "Oklahoma" | State == "Texas"
replace Region = 8 if State == "Arizona" | State == "Colorado" | State == "Idaho" | ///
State == "Montana" | State == "Nevada" | State == "New Mexico" | State == "Utah" | State == "Wyoming"
replace Region = 9 if State == "California" | State == "Oregon" | State == "Washington"
drop if State == "Hawaii" | State == "Alaska"

preserve
import excel using popdata_1985to1990.xlsx, firstrow clear
rename Column1 Abbreviation
tempfile pop
save `pop'
restore
merge 1:1 Abbreviation using `pop'
drop if _merge == 2
drop _merge

keep State FIPS Abbreviation pop* HPI1* Region
foreach year of numlist 1985/1990{
g popw_HPI`year' = pop`year'*HPI`year'/10000
}
collapse (sum) popw_HPI1985 popw_HPI1986 popw_HPI1987 popw_HPI1988 ///
popw_HPI1989 popw_HPI1990 pop1985 pop1986 pop1987 pop1988 ///
pop1989 pop1990, by(Region)
cap drop HPI_w*
foreach year of numlist 1985/1990{
g HPI_weighted`year' = 10000*popw_HPI`year'/pop`year'
}

summ HPI_weighted1990 if Region == 2
local NY_hpi = r(mean)
foreach year of numlist 1985/1990{
g HPI_weight_adj`year'= 100* HPI_weighted`year'/`NY_hpi'
}

keep HPI_weight_adj* Region pop1*
reshape long pop HPI_weight_adj, i(Region) j(Year)
save HPI_1985to1990_region.dta, replace

/*
Calculating Distance Matrix Between Regions

*/

import excel using PopCentroid_Region_1990.xlsx, firstrow clear
rename Region RegionName
rename REgionNum Region
g id = 61191
preserve
reshape wide RegionName Lat Long, i(id) j(Region)
tempfile widever
save `widever'
restore
merge m:1 id using `widever'
foreach i of numlist 1/9{
gen dist_region`i'=3963*acos(sin(Lat/57.2958)*sin(Lat`i'/57.2958) ///
+cos(Lat/57.2958)*cos(Lat`i'/57.2958)*cos(Long`i'/57.2958-Long/57.2958)) 
replace dist_region`i' = 0 if Region == `i'
}
foreach i of numlist 1/9{
drop Lat`i' Long`i' RegionName`i'
}

drop id _merge
save distancematrix_region.dta, replace
